﻿using System;
using System.Data;
using System.Data.SqlClient;

//public interface DataBaseOperate_Interface {
    
//    DataTable GetDataTable(string sqlstr);
    
//    DataRow GetDataRow(string sqlstr);
    
//    bool GetYesOrNo(string sqlstr);
    
//    object GetValue(string sqlstr);
    
//    bool ExecUpdate(string sqlstr);

//}
    
    public class DataBaseOperate_SQLServer {
        
        public static SqlDataAdapter objDataAdpter;
        
        public static SqlConnection objConnection;

        public const string DataConnStr = "Data Source=localhost;Initial Catalog=bestonepay;Integrated Security=True;packet size=4096;Pooling=true;Min Pool Size=1;Max Pool Size=8;";
        
        private static void DataConnInit() {
            if ((objConnection == null)) {
                objConnection = new System.Data.SqlClient.SqlConnection(DataConnStr);
                // Warning!!! Optional parameters not supported
            }
        }
        public static DataTable GetDataTable(string sqlstr, SqlParameter[] prams)
        {
            DataConnInit();
            DataTable dt = new DataTable("t1");
            try
            {
                if ((objConnection.State == ConnectionState.Closed))
                {
                    objConnection.Open();
                }
                objDataAdpter = new SqlDataAdapter(sqlstr, objConnection);
                objDataAdpter.SelectCommand.CommandType = CommandType.Text;  //执行类型：命令文本
                if (prams != null)
                {
                    foreach (SqlParameter parameter in prams)
                        objDataAdpter.SelectCommand.Parameters.Add(parameter);
                }
                objDataAdpter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                Log.Record(ex, ("产生错误的SQL语句:" + sqlstr));
                return null;
            }
        }

        public static DataTable GetDataTable(string sqlstr)
        {
            return GetDataTable(sqlstr,null);
        }
        
        public static DataRow GetDataRow(string sqlstr) {
            DataTable dt = GetDataTable(sqlstr);
            if ((!(dt == null) 
                        && (dt.Rows.Count > 0))) {
                return dt.Rows[0];
            }
            else {
                return null;
            }
        }
        
        public static bool GetYesOrNo(string sqlstr) {
            if ((GetValue(sqlstr) == null)) {
                return false;
            }
            else {
                return true;
            }
        }
        
        public static object GetValue(string sqlstr) {
            DataConnInit();
            object Result = null;
            try {
                if ((objConnection.State == ConnectionState.Closed)) {
                    objConnection.Open();
                }
                SqlCommand objCommand = new SqlCommand(sqlstr, objConnection);
                Result = objCommand.ExecuteScalar();
            }
            catch (System.Exception ex) { 
                Log.Record(ex, ("产生错误的SQL语句:" + sqlstr));
            }
            return Result;
        }

        
        public static bool ExecUpdate(string sqlstr) {
            DataConnInit();
            try {
                if ((objConnection.State == ConnectionState.Closed)) {
                    objConnection.Open();
                }
                SqlCommand objCommand = new SqlCommand(sqlstr, objConnection);
                objCommand.ExecuteNonQuery();
                return true;
            }
            catch (System.Exception ex) { 
                Log.Record(ex, ("产生错误的SQL语句:" + sqlstr));
                return false;
            }
        }


        public static bool ExecUpdate(SqlCommand objCommand)
        {
            DataConnInit();
            try
            {
                if ((objConnection.State == ConnectionState.Closed))
                {
                    objConnection.Open();
                }
                objCommand.Connection = objConnection;
                objCommand.ExecuteNonQuery();
                return true;
            }
            catch (System.Exception ex)
            {
                Log.Record(ex);
                return false;
            }
        }

        //操作前执行的存储过程
        public static int execPPaidAtonce(String userId, String costOrgId, String searchStr, String amountMoney, String userMoney,
            String coupon, String couponMoney, String payType, String payTime, String remark){
            //充值前操作
            String sql = "select cost_id, search_type1 from t_cost_organization where cost_organization_id = " + costOrgId;
            DataRow dataRow = GetDataRow(sql);
            Double handlingfeeMoney = 0.0;
            if (costOrgId.Equals("5") && System.Convert.ToDouble(amountMoney) > 500) {
                handlingfeeMoney = System.Convert.ToDouble(amountMoney) * 1.5 / 100;
            }
            String sqlAtonce = "exec p_paid_atonce @user_id=" + userId + ", "//用户ID
                                                                                 + "@cost_id=" + dataRow[0]+ ", " //cost_id
                                                                                 + "@cost_organization_id=" + costOrgId + ", "            //cost_organization_id（固定）
                                                                                 + "@search_type='" + dataRow[1] + "', "//查询类型
                                                                                 + "@search_str='" + searchStr + "', "//查询内容
                                                                                 + "@amount_money=" + amountMoney + ", "//充值
                                                                                 + "@user_money=" + userMoney + ", "//实付
                                                                                 + "@handlingfee_money=" + handlingfeeMoney.ToString() + ", "//用户手续费
                                                                                 +"@coupon="+coupon+", "//用户使用优惠券暂时为0
                                                                                 + "@coupon_money=" + couponMoney + ", "//优惠券抵扣金额暂时为0
                                                                                 + "@pay_type=" + payType + ", "//0：付费直通车和充值     1：每月一次付     2：月月自动扣    3：其他
                                                                                 + "@pay_time='" + payTime + "', "//账期日
                                                                                 + "@remark='" + remark + "' ";//备注
            DataTable dataTable = GetDataTable(sqlAtonce);
            return (int)dataTable.Rows[0][0];    
        }

        //操作后执行的存储过程
        public static void execPPaidSetStatus(String userPaidId, String serialNo, String itemNo, String remark, String operateStatus, String userIp, String adminId)
        {
            String sqlSetstatus = "exec p_paid_setstatus @user_paid_id =" + userPaidId + ", "
                                                                                            + "@serial_no='" + serialNo + "', "
                                                                                            + "@item_no='" + itemNo + "', "
                                                                                            + "@remark='" + remark + "', "
                                                                                            + "@operate_status=" + operateStatus + ", "
                                                                                            + "@user_ip='" + userIp + "', "
                                                                                            +"@admin_id="+adminId+" ";

            GetDataTable(sqlSetstatus);
        }

    }

